var queues = require('mysql-queues');
const DEBUG = true;
	var selectSys = function(connection, callback) {
		var sql = 'select * from system';
		connection.query(sql, function(err, rows) {
			if (err){
        console.log(err);
        console.log(err.stack);
      }
			callback(rows);
			connection.release();
		});
	},
  //system表和employee表关联查询所有体系
  selSys = function(connection, callback) {
    var sql = "select s.*, e.name from system  s left join employee e on s.sysManagerId = e.id";
    connection.query(sql, function(err, rows) {
      if(err){
        console.log(err);
        console.log(err.stack);
      }
      callback(rows);
      connection.release();
    });
  },

	addSys = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'insert into system(sysName,sysManagerId) values(?,?)';
		var trans = connection.startTransaction();
		trans.query(sql, [data.sysName, data.sysManagerId], function(err, rows) {
			if (err) {
				console.log(err);
        console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log(info);
				});
			}
			// console.log('addSys---rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute addSys');
		connection.release();
	},

  deleteSys = function(connection,id,callback){
  	queues(connection,DEBUG);
  	var sql = 'delete from system where id=?';
  	var trans = connection.startTransaction();
  	trans.query(sql,id,function(err,rows){
  		if(err){
  			console.log(err);
        console.log(err.stack);
        trans.rollback();
  			// trans.commit(function(err,info){
  			// 	console.log(info);
  			// });

  		}
      trans.commit(function(err,info){
        console.log(info);
      });
  		console.log('rows',rows);
  		callback(rows);
  	});
  	trans.execute();
  	console.log('execute deleteSys');
  	connection.release();
  },

  updateSys = function(connection,data,callback){
  	queues(connection,DEBUG);
  	var sql = 'update system set sysName=?,sysManagerId=? where id=?';
  	var trans = connection.startTransaction();
  	trans.query(sql,[data.sysName,data.sysManagerId,data.id],function(err,rows){
  		if(err){
  			console.log(err);
        console.log(err.stack);
        trans.rollback();
  			// trans.commit(function(err,info){
  			// 	console.log(info);
  			// });
  		}
      trans.commit(function(err,info){
          console.log(info);
        });
  		console.log('rows',rows);
  		callback(rows);
  	});
  	trans.execute();
  	console.log('execute updateSys');
  	connection.release();
  },

  selSysByName = function(connection, sysName, callback) {
    var sql = "select * from system where sysName=?";
    connection.query(sql, sysName, function(err, rows) {
      if(err) {
        console.log(err);
        console.log(err.stack);
      }
      callback(rows);
      connection.release();
    });
  };

  findById = function(connection,id,callback){
  	var sql = 'select * from system where id=?';
  	connection.query(sql, id, function(err, rows) {
			if (err){
        console.log(err);
        console.log(err.stack);
      }
			callback(rows);
			connection.release();
		});
  };

module.exports = {
	selectSys: selectSys
	,addSys: addSys
	,deleteSys: deleteSys
	,updateSys: updateSys
	,findById: findById
  ,selSysByName: selSysByName
  ,selSys: selSys
};